package com.herui.ota.utils;

import com.herui.ota.exception.ServiceException;
import io.swagger.annotations.ApiModelProperty;
import lombok.extern.log4j.Log4j2;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.tomcat.util.http.fileupload.FileItem;
import org.apache.tomcat.util.http.fileupload.disk.DiskFileItemFactory;
import org.springframework.http.MediaType;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import java.io.*;
import java.lang.reflect.Field;
import java.util.*;


/**
 * @Author: Akrio
 * @Date: 2021-01-26 15:13
 */
@Log4j2
public class ExcelFileUtil {


    private static FormulaEvaluator evaluator;


    /**
     * 将任何Excel转为Map，K为第一行表头，V为每一行表头下的值
     *
     * @param file
     * @return
     */

    public static List<List<Map<String, String>>> analysisExcel(MultipartFile file) {
        // 保存文件
        DownloadUtil.saveExcel(file);
        List<List<Map<String, String>>> tList = new ArrayList<>();
        Workbook wb = null;
        String fileName = file.getOriginalFilename();
        String extString = fileName.substring(fileName.lastIndexOf("."));
        Sheet sheet = null;
        List<Map<String, String>> list = null;
        Row row = null;
        String cellData = null;
        InputStream is = null;
        try {
            is = file.getInputStream();
            if (".xls".equals(extString)) {
                wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(extString)) {
                wb = new XSSFWorkbook(is);
            } else {
                throw new ServiceException("excel读取错误");
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            log.error(e.getMessage());
            throw new ServiceException("00888","无法解析Excel");
        }
        if (wb != null) {
            evaluator = wb.getCreationHelper().createFormulaEvaluator();
            // 用来存放表中数据
            // 获取sheet第一行
            // sheet = wb.getSheetAt(0);
            int sheetNum = 0;
            for (Iterator<Sheet> iterator = wb.sheetIterator(); iterator.hasNext();) {
//                if (sheetNum > 0){
//                    System.out.println(sheetNum);
//                    iterator.next();
//                    continue;
//                }

                list = new ArrayList<Map<String, String>>();
                sheet = iterator.next();
                // 获取最大行数
                int rownum = sheet.getPhysicalNumberOfRows()+2;
                // 获取第一个sheet
                row = sheet.getRow(0);
                if(row==null)
                    continue;
                // 获取最大列数
                int colnum = row.getPhysicalNumberOfCells();
                String[] columns = new String[colnum];

                for (int i = 0; i < colnum; i++) {
                    columns[i] = (String)getCellFormatValue(row.getCell(i));
                }
                Integer count=0;
                for (int i = 1; i < rownum; i++) {// 从第二行开始
                    Map<String, String> map = new LinkedHashMap<String, String>();
                    row = sheet.getRow(i);
                    if (row != null && row.getCell(0)!=null) {// 若存在数据
                        count=0;
                        log.info("row:{}",row.getCell(0));
                        if (!StringUtils.isEmpty(row.getCell(0).getStringCellValue())){
                            for (int j = 0; j < colnum; j++) {
                                cellData = (String)getCellFormatValue(row.getCell(j));
                                map.put(columns[j], cellData);
                            }
                        }
                    } else {
                        count++;
                        if(count>10){
                            break;
                        }else {
                            continue;
                        }

                    }
                    list.add(map);
                }
                tList.add(list);
                sheetNum++;
            }

        }
        return tList;
    }


    /**
     * 将任何list转为Excel的byte数组
     *
     * @param list
     * @param <T>
     * @return
     */
    public static <T> byte[] createExcel(List<T> list) {
        byte[] bytes = new byte[0];
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("sheet1");// 表名
        HSSFCellStyle style = wb.createCellStyle();
        HSSFRow row = sheet.createRow(0);// 第一行
        if(list.size()>0){
            Field[] declaredFields = list.get(0).getClass().getDeclaredFields();// 获取list成员对象
            List<String> tatle = new ArrayList<>();
            for (Field field : declaredFields) {
                ApiModelProperty aa = field.getAnnotation(ApiModelProperty.class);
                if (aa != null)
                    tatle.add(aa.value());
            }

            for (int i = 0; i < tatle.size(); i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(tatle.get(i));// 成员名称
                cell.setCellStyle(style);
                sheet.setColumnWidth(i, (tatle.get(i).length()) * 1024); // 设置宽
            }

            try {
                for (int i = 0; i < list.size(); i++) { // 第二行开始的N行
                    HSSFRow rows = sheet.createRow(i + 1);
                    rows.setHeight((short)400); // 设置高度
                    Field[] fields = list.get(i).getClass().getDeclaredFields();
                    for (int j = 0; j < tatle.size(); j++) {
                        if (!fields[j].isAccessible())
                            fields[j].setAccessible(true);
                        HSSFCell cell = rows.createCell(j); // 创建单元格
                        cell.setCellStyle(style);
                        Object o = fields[j].get(list.get(i));
                        if (o == null) {
                            cell.setCellValue("");
                        } else {
                            cell.setCellValue(o.toString());
                        }
                    }
                }
                ByteArrayOutputStream bos = new ByteArrayOutputStream();
                wb.write(bos);
                bos.close();
                bos.flush();
                bytes = bos.toByteArray();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return bytes;
    }




//    private static <T> List<T> readFileToList(MultipartFile file, Class<T> clazz) {
//        List<T> list = new ArrayList<>();
//        // 解析Excel
//        List<List<Map<String, String>>> tList = ExcelFileUtil.analysisExcel(file, clazz);
//        for (List<Map<String, String>> maps : tList) {
//            for (Map<String, String> map : maps) {
//                if (map.get("itemNo") != null && !("".equals(map.get("itemNo").trim()))) {
//                    try {
//                        String jsonString = JSON.toJSONString(map);
//                        T o = JSON.parseObject(jsonString, clazz);
//                        list.add(o);
//                    } catch (Exception e) {
//                        e.printStackTrace();
//                    }
//                }
//            }
//        }
//        return list;
//    }

    private static Object getCellFormatValue(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            // 判断cell类型
            switch (cell.getCellTypeEnum()) {
                case NUMERIC: {
                    if (DateFormatUtil.isCellDateFormatted(cell)) {
                        cellValue = String.valueOf(cell.getDateCellValue().getTime());
                        break;
                    }
                    // 判断cell是否为日期格式
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        cellValue = String.valueOf(cell.getDateCellValue().getTime());
                    } else {
                        if (cell.getNumericCellValue() % 1.0 == 0) {
                            cellValue = String.valueOf((int)cell.getNumericCellValue());
                        } else {
                            cellValue = String.valueOf(cell.getNumericCellValue());
                        }
                    }
                    break;
                }
                case FORMULA: {
                    cellValue = evaluator.evaluate(cell).getStringValue();
                    break;
                }
                case STRING: {
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }



    /**
     * 将任何list转为Excel的byte数组 传入任何String数组，若有单元格内容与传入的数组内容完全一致 则此行变为灰色
     *
     * @param tList
     * @param <T>
     * @return
     */
    public static <T> byte[] createExcel2(List<List<T>> tList) {
        if (tList == null || tList.size() == 0)
            return new byte[0];
        byte[] bytes = new byte[0];
        HSSFWorkbook wb = new HSSFWorkbook();

        // 默认
        HSSFCellStyle style = wb.createCellStyle();
        style.setWrapText(true);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.LEFT);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN); // 下边框
        style.setBorderLeft(BorderStyle.THIN); // 左边框
        style.setBorderRight(BorderStyle.THIN); // 右边框
        style.setBorderTop(BorderStyle.THIN); // 上边框
        int sheetNum = 0;
        try {
            for (List<T> list : tList) {

                boolean projectName = true;
                HSSFSheet sheet = wb.createSheet("sheet" + sheetNum);// 表名;
                HSSFRow row = sheet.createRow(0);// 第一行
                // 获取list第一个的class类型
                Field[] declaredFields = list.get(0).getClass().getDeclaredFields();
                List<String> tatle = new ArrayList<>();
                for (Field field : declaredFields) {
                    ApiModelProperty aa = field.getAnnotation(ApiModelProperty.class);
                    if (aa != null)
                        tatle.add(aa.value());
                }
                for (int i = 0; i < tatle.size(); i++) {
                    HSSFCell cell = row.createCell(i);
                    cell.setCellValue(tatle.get(i));// 成员名称

                        cell.setCellStyle(style);

                    sheet.setColumnWidth(i, tatle.get(i).length() * 1024); // 设置宽
                }

                for (int i = 0; i < list.size(); i++) { // 第二行开始的N行
                    HSSFRow rows = sheet.createRow(i + 1);
                    rows.setHeight((short)400); // 设置高度
                    Field[] fields = list.get(i).getClass().getDeclaredFields();
                    for (int j = 0; j < tatle.size(); j++) {
                        if (!fields[j].isAccessible())
                            fields[j].setAccessible(true);
                        HSSFCell cell = rows.createCell(j); // 创建单元格
                        cell.setCellStyle(style);
                        Object o = fields[j].get(list.get(i));
                        if (o == null) {
                            cell.setCellValue("");
                        } else {
                            cell.setCellValue(o.toString());
                        }
                    }
                }
                sheetNum++;
            }
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            wb.write(bos);
            bos.close();
            bos.flush();
            bytes = bos.toByteArray();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return bytes;
    }
    /**
     * 将任何list转为Excel的byte数组 传入任何String数组，若有单元格内容与传入的数组内容完全一致 则此行变为灰色
     *
     * @param map
     * @param <T>
     * @return
     */
    public static <T> byte[] createExcel3(Map<String,List<T>> map)  {


        byte[] bytes = new byte[0];
        HSSFWorkbook wb = new HSSFWorkbook();

        // 默认
        HSSFCellStyle style = wb.createCellStyle();
        style.setWrapText(true);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.LEFT);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN); // 下边框
        style.setBorderLeft(BorderStyle.THIN); // 左边框
        style.setBorderRight(BorderStyle.THIN); // 右边框
        style.setBorderTop(BorderStyle.THIN); // 上边框
        int sheetNum = 0;
        try {
            for (String key : map.keySet()) {
                List<T> list=map.get(key);
                boolean projectName = true;
                HSSFSheet sheet = wb.createSheet(key);// 表名;
                HSSFRow row = sheet.createRow(0);// 第一行
                // 获取list第一个的class类型
                if(list!=null && list.size()>0){
                    Field[] declaredFields = list.get(0).getClass().getDeclaredFields();
                    List<String> tatle = new ArrayList<>();
                    for (Field field : declaredFields) {
                        ApiModelProperty aa = field.getAnnotation(ApiModelProperty.class);
                        if (aa != null)
                            tatle.add(aa.value());
                    }
                    for (int i = 0; i < tatle.size(); i++) {
                        HSSFCell cell = row.createCell(i);
                        cell.setCellValue(tatle.get(i));// 成员名称

                        cell.setCellStyle(style);

//                    sheet.setColumnWidth(i, tatle.get(i).length() * 512); // 设置宽
                        sheet.setColumnWidth(i, 10240); // 设置宽
                    }
                    if(list.size()>60000){
                        for (int i = 0; i < 60000; i++) { // 第二行开始的N行
                            HSSFRow rows = sheet.createRow(i + 1);
                            rows.setHeight((short)400); // 设置高度
                            Field[] fields = list.get(i).getClass().getDeclaredFields();

                            for (int j = 0; j < tatle.size(); j++) {
                                if (!fields[j].isAccessible())
                                    fields[j].setAccessible(true);
                                HSSFCell cell = rows.createCell(j); // 创建单元格
                                cell.setCellStyle(style);
                                Object o = fields[j].get(list.get(i));
                                if (o == null) {
                                    cell.setCellValue("");
                                } else {
                                    cell.setCellValue(o.toString());
                                }
                            }
                        }
                    }else {
                        for (int i = 0; i < list.size(); i++) { // 第二行开始的N行
                            HSSFRow rows = sheet.createRow(i + 1);
                            rows.setHeight((short)400); // 设置高度
                            Field[] fields = list.get(i).getClass().getDeclaredFields();

                            for (int j = 0; j < tatle.size(); j++) {
                                if (!fields[j].isAccessible())
                                    fields[j].setAccessible(true);
                                HSSFCell cell = rows.createCell(j); // 创建单元格
                                cell.setCellStyle(style);
                                Object o = fields[j].get(list.get(i));
                                if (o == null) {
                                    cell.setCellValue("");
                                } else {
                                    cell.setCellValue(o.toString());
                                }
                            }
                        }
                    }

                    sheetNum++;
                }
            }
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            wb.write(bos);
            bos.close();
            bos.flush();
            bytes = bos.toByteArray();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return bytes;
    }


    static class Person{
        @ApiModelProperty("姓名")
        private String name;
        @ApiModelProperty("年龄")
        private Integer age;

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public Integer getAge() {
            return age;
        }

        public void setAge(Integer age) {
            this.age = age;
        }
    }

}
